﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

namespace PPAP.DAO
{
    public class T_FileDAO
    {
        public T_FileDAO() { }

        /// <summary>
        /// 根据附件类型和id得到附件列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataSet GetFileList(string fileID, int types)
        {
            string sql = "select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,filesize,rownum from T_filetable where F_FileID='" + fileID + "' and F_Flag=" + types + " and isdelete=0";
            return DbHelperOra.Query(sql);
        }

        /// <summary>
        /// 根据部件id得到附件列表
        /// </summary>
        /// <param name="fileID"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public DataSet GetFileList(string fileID)
        {
            string sql = "select F_ID,F_FileID,F_FileName,F_url,F_Detail,UpTime,filesize,rownum,a.attachmenttype,F_Flag,a.sx from T_filetable t left join attachmenttype a on t.F_Flag=a.id where F_FileID='" + fileID + "'  and isdelete=0";
            return DbHelperOra.Query(sql);
        }


        /// <summary>
        /// 根据id得到上传文件路径
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataSet Getpath(int id)
        {
            string sql = "select * from T_filetable where F_ID =" + id + "";
            return DbHelperOra.Query(sql);
        }
        /// <summary>
        /// 根据附件id删除对应附件信息
        /// </summary>
        /// <param name="F_ID"></param>
        /// <returns></returns>
        public bool DeleteFile(string F_ID)
        {
            string sql = "delete from T_filetable where F_ID='" + F_ID + "'";
            sql = "update t_filetable set isdelete=1 where F_ID='" + F_ID + "'";
            return DbHelperOra.ExecuteSql(sql) > 0;
        }
        /// <summary>
        /// 添加一条附件
        /// </summary>
        /// <param name="F_FileID"></param>
        /// <param name="F_Filename"></param>
        /// <param name="path"></param>
        /// <param name="types"></param>
        /// <returns></returns>
        public bool InsertFile(string F_FileID, string F_Filename, string path, int types, string url,string filesize,string remarks,string upby,string filetype,string pparid,string fileversion)
        {
            int F_ID = DbHelperOra.GetMaxID("F_ID", "t_filetable");
            string sql = @"insert into t_filetable(F_ID,F_FileID,F_Filename,F_Detail,F_Flag,F_url,UpTime,filesize,remarks,UPBY,filetype,pparno,fileversion,isdelete) values('" + F_ID + "','" + F_FileID + "','" + F_Filename + "','" + path + F_Filename + "'," + types + ",'" + url + "',(select sysdate from dual),'" + filesize + "','" + remarks + "','" + upby + "','"+filetype+"','"+pparid+"','"+fileversion+"',0)";
            return DbHelperOra.ExecuteSql(sql)>0;
        }

        /// <summary>
        /// 文件管理
        /// </summary>
        /// <param name="sqlwhere"></param>
        /// <returns></returns>
        public DataSet GetListForSearch(string sqlwhere)
        {
            string sql = @"select rownum,t.* from (select a.attachmenttype,t.upby,UpTime,'' d1,p.pparno,r.bdntpartno,t.filetype,filesize,'' d,F_url,F_FileName,t.isdelete from T_filetable t left join attachmenttype a on t.F_Flag=a.id 
                            left join ppar p on t.pparno=p.id 
                            left join part r on p.partid=r.id
                            where 1=1  ";
            sql += sqlwhere;
            sql += " order by uptime )t";
            return DbHelperOra.Query(sql);
        }
    }
}
